﻿using GM_Shop.Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GenericSide
{
    public class SqlHelper
    {
        static string sqlKey;

        static SqlHelper()
        {
            sqlKey = "Data Source=.;Initial Catalog=GM_Shop;Integrated Security=True";
        }

        public static List<T> Select<T>() where T : new()
        {
            Type type = typeof(T);
            string sql = $"select {string.Join(",", type.GetProperties().Select(u => u.Name))} from {type.GetTableName<T>()}";

            return Find<T>(sql);
        }

        public static List<T> Select<T>(int id) where T : new()
        {
            Type type = typeof(T);
            string sql = $"select {string.Join(",", type.GetProperties().Select(u => u.Name))} from {type.GetTableName<T>()} where {type.GetProperties()[0].Name} = {id}";

            return Find<T>(sql);
        }

        public static List<T> Select<T>(SqlParameter[] parat) where T : new()
        {
            Type type = typeof(T);
            var peop = type.GetProperties();

            StringBuilder chileSql = new StringBuilder();

            for (int i = 0; i < parat.Length; i++)
            {
                var indexPeop = peop.FirstOrDefault(u => u.Name == parat[i].ParameterName);
                if (indexPeop != null)
                {
                    chileSql.Append(indexPeop.Name).Append("=@").Append(indexPeop.Name).Append(" and ");
                }
            }

            string sql = $"select {string.Join(",", peop.Select(u => u.Name))} from {type.GetTableName<T>()} where {chileSql.ToString().Substring(0, chileSql.ToString().LastIndexOf("and"))}";

            return Find<T>(sql, parat);
        }

        public static List<T> Select<T>(string sql)
        {
            return Find<T>(sql);
        }

        public static int Insert<T>(T data)
        {
            Type type = data.GetType();
            var peop = type.GetProperties();

            var list = peop.ToList();
            list.RemoveAt(0);
            string chileSql = string.Join(",@", list.Select(u => u.Name));

            string sql = $"insert into {type.GetTableName<T>()} values (@{ chileSql })";

            SqlParameter[] parat = new SqlParameter[peop.Length - 1];
            for (int i = 0; i < parat.Length; i++)
                parat[i] = new SqlParameter(list[i].Name, list[i].GetValue(data));

            using (SqlConnection conn = new SqlConnection(sqlKey))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(parat);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int Delect<T>(int id)
        {
            Type type = typeof(T);
            string sql = $"delete from {type.GetTableName<T>()} where {type.GetProperties()[0].Name} = {id}";
            using (SqlConnection conn = new SqlConnection(sqlKey))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                return cmd.ExecuteNonQuery();
            }
        }

        public static int Update<T>(T data) where T : new()
        {
            Type type = data.GetType();
            var peop = type.GetProperties();

            SqlParameter[] parat = new SqlParameter[peop.Length];
            for (int i = 0; i < parat.Length; i++)
                parat[i] = new SqlParameter(peop[i].Name, peop[i].GetValue(data));

            var list = peop.ToList();
            list.RemoveAt(0);
            StringBuilder chileSql01 = new StringBuilder();
            for (int i = 0; i < list.Count; i++)
            {
                chileSql01.Append(list[i].Name);
                chileSql01.Append("=@");
                chileSql01.Append(list[i].Name);
                if (i != list.Count - 1)
                    chileSql01.Append(",");
            }

            string sql = $"update {type.GetTableName<T>()} set { chileSql01 } where {peop[0].Name} = @{peop[0].Name}";

            using (SqlConnection conn = new SqlConnection(sqlKey))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddRange(parat);
                return cmd.ExecuteNonQuery();
            }
        }

        private static List<T> Find<T>(string sql, SqlParameter[] parat = null)
        {
            List<T> list = new List<T>();
            Type type = typeof(T);
            using (SqlConnection conn = new SqlConnection(sqlKey))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (parat != null)
                    cmd.Parameters.AddRange(parat.ToArray());
                var data = cmd.ExecuteReader();
                while (data.Read())
                {
                    T t = Activator.CreateInstance<T>();
                    foreach (var item in type.GetProperties())
                        item.SetValue(t, data[item.Name]);
                    list.Add(t);
                }
                return list;
            }
        }
    }
}
